﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Text;

namespace YunShop_WebAPI.BLL
{
    public class DBService
    {

        /// <summary>
        /// 表值自动分页查询，必须有主键sPrimary,不包括统计的查询
        /// </summary>
        /// <param name="sPrimary">主键</param>
        /// <param name="sFieldList">查询的字段集合</param>
        /// <param name="sql">查询的表关联集合</param>
        /// <returns></returns>
        public static string GetSelectPageSQL(string sPrimary, string sFieldList, string sql)
        {
            //@PageSize每页多少条记录
            //@PageIndex指定当前为第几页
            //Declare @PageSize int,@PageIndex int Set @PageSize = 15 Set @PageIndex = 0
            string msql = string.Format(@"Declare @RowCount int 
                    Declare @StartRecord int
                    Declare @EndRecord int 
                    if (@PageSize = 0) Set @PageSize = 99999999
                    set @StartRecord = @PageIndex*@PageSize
                    set @EndRecord = @StartRecord + @PageSize
                    Select @RowCount=Count({0}) {2}
                    select *,@RowCount as FRowCount from (select row_number() over (order by {0}) as FRowId,
                    {1} {2}) as t where FRowId between @StartRecord+1 and @EndRecord", sPrimary, sFieldList, sql);
            return msql;
        }

        /// <summary>
        /// 表值自动分页查询，没有主键sPrimary统计的查询
        /// </summary>
        /// <param name="sTempDataTable">统计后生成的临时表</param>
        /// <param name="sFieldList">统计的字段集合</param>
        /// <param name="sql">统计的表关联集合</param>
        /// <returns></returns>
        public static string GetNotPrimaryPageSQL(string sTempDataTable, string sFieldList, string sql)
        {
            //@PageSize每页多少条记录
            //@PageIndex指定当前为第几页
            //Declare @PageSize int,@PageIndex int Set @PageSize = 15 Set @PageIndex = 0
            string msql = string.Format(@"Declare @RowCount int 
                Declare @StartRecord int
                Declare @EndRecord int 
                if (@PageSize = 0) Set @PageSize = 2
                set @StartRecord = @PageIndex*@PageSize
                set @EndRecord = @StartRecord + @PageSize
                SELECT Identity(int,1,1) as FRowId, {1}
                INTO {0} {2}
                Select @RowCount=Count(FRowId) FROM {0}
                select * from {0} where FRowId between @StartRecord and @EndRecord
                drop table {0}", sTempDataTable, sFieldList, sql);
            return msql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        public static string GetValidJson(string json)
        {
            if (string.IsNullOrEmpty(json)) return "[]";
            string myjson = "";
            try
            {
                if (json.Substring(0, 1) != "[")
                    myjson = "[" + json;
                else
                    myjson = json;
                if (json.Substring(json.Length - 1, 1) != "]")
                    myjson = myjson + "]";
            }
            catch
            {
            }
            return myjson;
        }

        //程序结束
    }
}